﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for FleetPackageDB
/// </summary>
namespace CE.CarInsurance
{
    public class FleetPackageDB
    {
        public Int64 Insert(FleetPackage fleetpackage)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXCRMFleetPackageCarDetailsInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@FleetCarId", fleetpackage.FleetCarId);
            _sPara.Add("@CompanyId", fleetpackage.CompanyId);
            _sPara.Add("@ProviderCompanyId", fleetpackage.ProviderCompanyId);
            _sPara.Add("@Title", fleetpackage.Title);
            _sPara.Add("@CustomerType", fleetpackage.CustomerType);
            _sPara.Add("@CustomerName", fleetpackage.CustomerName);
            _sPara.Add("@Name", fleetpackage.Name);
            _sPara.Add("@DOB", fleetpackage.DOB);
            _sPara.Add("@Gender", fleetpackage.Gender);
            _sPara.Add("@Occupation", fleetpackage.Occupation);
            _sPara.Add("@ContactNo", fleetpackage.ContactNo);
            _sPara.Add("@OfficePhoneNo", fleetpackage.OfficePhoneNo);
            _sPara.Add("@IdentificationNo", fleetpackage.IdentificationNo);
            _sPara.Add("@PresentAddress", fleetpackage.PresentAddress);
            _sPara.Add("@SubDistrict", fleetpackage.SubDistrict);
            _sPara.Add("@District", fleetpackage.District);
            _sPara.Add("@Province", fleetpackage.Province);
            _sPara.Add("@PostalCode", fleetpackage.PostalCode);
            _sPara.Add("@Package", fleetpackage.Package);
            _sPara.Add("@NoOfDriver", fleetpackage.NoOfDriver);
            _sPara.Add("@InsuranceType", fleetpackage.InsuranceType);
            _sPara.Add("@GarageType", fleetpackage.GarageType);
            _sPara.Add("@CarYear", fleetpackage.CarYear);
            _sPara.Add("@CarModel", fleetpackage.CarModel);
            //_sPara.Add("@Size", fleetpackage.Size);
            //_sPara.Add("@VehicleWeight", fleetpackage.VehicleWeight);
            //_sPara.Add("@NumberofSeat", fleetpackage.NumberofSeat);
            _sPara.Add("@Beneficiary", fleetpackage.Beneficiary);
            _sPara.Add("@VehicleType", fleetpackage.VehicleType);
            _sPara.Add("@SumInsured", fleetpackage.SumInsured);
            _sPara.Add("@NCB", fleetpackage.NCB);
            _sPara.Add("@CurrentInsurer", fleetpackage.CurrentInsurer);
            _sPara.Add("@CarPlate", fleetpackage.CarPlate);
            _sPara.Add("@CarPlateProvince", fleetpackage.CarPlateProvince);
            _sPara.Add("@EngineNumber", fleetpackage.EngineNumber);
            _sPara.Add("@ChassisNumber", fleetpackage.ChassisNumber);
            //_sPara.Add("@IsModification", fleetpackage.IsModification);
            //_sPara.Add("@IsClaim", fleetpackage.IsClaim);
            _sPara.Add("@CoverageStart", fleetpackage.CoverageStart);
            _sPara.Add("@Premium", fleetpackage.Premium);
            _sPara.Add("@IsCTPL", fleetpackage.IsCTPL);
            _sPara.Add("@CTPL", fleetpackage.CTPL);
            _sPara.Add("@CTPLStart", fleetpackage.CTPLStart);
            _sPara.Add("@TotalCollectedPremium", fleetpackage.TotalCollectedPremium);
            _sPara.Add("@TotalPremium", fleetpackage.TotalPremium);
            _sPara.Add("@TermPremium", fleetpackage.TermPremium);
            _sPara.Add("@PaymentStatus", fleetpackage.PaymentStatus);
            _sPara.Add("@QuoteRefNo", fleetpackage.QuoteRefNo);
            _sPara.Add("@Status", fleetpackage.Status);
            _sPara.Add("@RefCEUserId", fleetpackage.RefCEUserId);
            _sPara.Add("@AgentId", fleetpackage.AgentId);
            _sPara.Add("@AdminId", fleetpackage.AdminId);
            _sPara.Add("@Info1", fleetpackage.Info1); // Application Type
            _sPara.Add("@Info2", fleetpackage.Info2); // Last Year Policy No.
            _sPara.Add("@Info3", fleetpackage.Info3);
            _sPara.Add("@IsCreditCardOwner", fleetpackage.IsCreditCardOwner); // tblCEUsrProfile table Ninfo5 field
            _sPara.Add("@Info4", fleetpackage.Info4); //Accessories or Modifications Cost
            _sPara.Add("@Info8", fleetpackage.Info8); //Agent Remarks
            _sPara.Add("@PromotionCode", fleetpackage.PromotionCode);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public DataSet GetCarDetails(Int64 FleetCarId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "sprocCXCRMFleetPackageCarDetailsSelectedItemByCarId";

            SortedList _spara = new SortedList();
            _spara.Add("@FleetCarId", FleetCarId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetCarDetails(Int64 FleetCarId, Int64 CompanyId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "sprocCXCRMFleetPackageCarDetailsSelectedItemByCarId";

            SortedList _spara = new SortedList();
            _spara.Add("@FleetCarId", FleetCarId);
            _spara.Add("@CompanyId", CompanyId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetFleetApplication(Int64 FleetCarId, Int64 ProviderId, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "sprocCXCRMFleetApplicationSelectItem";

            SortedList _spara = new SortedList();
            _spara.Add("@FleetCarId", FleetCarId);
            _spara.Add("@ProviderCompanyId", ProviderId);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetLastYearCarDetails(Int64 FleetCarId, string LYrQuoteRefNo)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "sprocCXCRMFleetPackageExsitingItemSelectByQuoteRefNo";

            SortedList _spara = new SortedList();
            _spara.Add("@FleetCarId", FleetCarId);
            _spara.Add("@LYrQuoteRefNo", LYrQuoteRefNo);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public Int64 UpdateApplicationCancelStatus(Int64 FleetCarId, Int64 ProviderId, Int64 AgentId, Int64 AdminId, Int16 Step, string Remarks)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMFleetApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@FleetCarId", FleetCarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@AdminId", AdminId);
            _sPara.Add("@Step", Step);
            _sPara.Add("@Remarks", Remarks);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateApplicationRetrieveStatus(Int64 FleetCarId, Int64 ProviderId, Int64 AgentId, Int64 AdminId, Int16 Step, String QuoteRefNo)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMFleetApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@FleetCarId", FleetCarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@AdminId", AdminId);
            _sPara.Add("@Step", Step);
            _sPara.Add("@QuoteRefNo", QuoteRefNo);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateExcelName(Int64 FleetCarId, string Excel)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMFleetExcelNameUpdatedSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@FleetCarId", FleetCarId);
            _sPara.Add("@Excel", Excel);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

    }
}